﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceIndexDefrag]
#-- Purpose:		Provides a selective mechanism for index defragging instead
#--					of the brute force attack of the maintenance plan's
#--					integrity checks and optimizations.
#--	Last Update:	01/26/2012
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		dbo.spMaintenance_Indexes
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceIndexDefrag]
(
	@database_name	sysname,
	@min_pages		int = 1000
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@sFragChk2k5 nvarchar(4000), @sSQL nvarchar(4000), @sAuditSQL nvarchar(MAX), @enterprise bit
DECLARE @tDefrag TABLE (IndexName sysname, OwnerName sysname, TableName sysname, defragStyle varchar(200), fragPct float)

--- Determine the state of the database
EXEC	@enterprise = dbo.spIsEnterpriseSQL

--- Define the query for checking fragmentation
---    NOTE: 5-30 for reorg and >30 for rebuild is straight out of BOL
SELECT	@sFragChk2k5 = '
		USE [' + @database_name + '];
		DECLARE		@db_id int
		SET			@db_id = DB_ID()
		SELECT		DISTINCT
					i.name IndexName,
					user_name(t.schema_id) OwnerName,
					t.name TableName,
					(CASE WHEN a.avg_fragmentation_in_percent <= 30 
							AND (
								EXISTS (SELECT * FROM ' + DB_NAME() + '.dbo.vwIndexMaintenanceAudit WHERE (mnt_index_name = i.name COLLATE SQL_Latin1_General_CP1_CI_AS) AND mnt_action_date >= DATEADD(dd, -7, GETDATE()) AND rebuilds_in_last_week > 0)
								OR (SELECT COUNT(*) FROM ' + DB_NAME() + '.dbo.vwIndexMaintenanceAudit WHERE (mnt_index_name = i.name COLLATE SQL_Latin1_General_CP1_CI_AS) AND mnt_action_date >= DATEADD(dd, -7, GETDATE())) < 6
							)
							THEN ''REORGANIZE;''
						  WHEN ' + CAST(@enterprise as CHAR(1)) + ' = 1 
								AND (
									SELECT		CAST(COUNT(*) as bit)
									FROM		sys.columns c (NOLOCK)
									LEFT JOIN	sys.index_columns ic (NOLOCK) ON c.object_id = ic.object_id
												AND c.column_id = ic.column_id
												AND ic.index_id = a.index_id
									LEFT JOIN	sys.indexes ii (NOLOCK) ON ii.object_id = c.object_id
												AND ii.index_id = a.index_id
									WHERE		c.object_id = a.object_id
												AND (
													ii.type IN (3, 4) -- 3=XML, 4=Spatial
													OR (
														ii.type = 1  --- Clustered indexes must look at all columns in the table
														AND (
															c.system_type_id IN (
																SELECT system_type_id FROM sys.types WHERE name IN (''text'', ''ntext'', ''image'')
															)
															OR (
																c.max_length = -1 
																AND c.system_type_id IN (
																	SELECT system_type_id FROM sys.types WHERE name IN (''varchar'', ''nvarchar'', ''varbinary'', ''xml'')
																)
															)
														)
													)
													OR (  -- Indexes that are not Clustered need only look at columns contained in the index
														ii.type NOT IN (1, 3, 4)
														AND ic.object_id IS NOT NULL
														AND (
															c.system_type_id IN (
																SELECT system_type_id FROM sys.types WHERE name IN (''text'', ''ntext'', ''image'')
															)
															OR (
																c.max_length = -1 
																AND c.system_type_id IN (
																	SELECT system_type_id FROM sys.types WHERE name IN (''varchar'', ''nvarchar'', ''varbinary'', ''xml'')
																)
															)
														)
													)
												)
								) = 0 ---- Do not allow online rebuild on indexes that contain blob data
									THEN ''REBUILD WITH (ONLINE=ON);''
						  ELSE ''REBUILD;''
						  END) defragStyle,
					a.avg_fragmentation_in_percent
		FROM		sys.dm_db_index_physical_stats (@db_id, NULL, NULL, NULL, NULL) AS a
		JOIN		sys.tables AS t ON a.object_id = t.object_id
		JOIN		sys.indexes AS i ON a.object_id = i.object_id
					AND a.index_id = i.index_id
					AND i.index_id > 0
		JOIN		sys.dm_db_index_usage_stats ius ON ius.object_id = i.object_id
					AND i.index_id = ius.index_id
					AND (
						ius.last_user_lookup > DATEADD(dd, -7, GETDATE())
						OR ius.last_user_scan > DATEADD(dd, -7, GETDATE())
						OR ius.last_user_seek > DATEADD(dd, -7, GETDATE())
					)
					---Note: Still Reviewing a way to filter further by the number and type of uses
					---AND (ius.user_seeks + ius.user_scans + ius.user_lookups) >= ius.user_updates
		WHERE		a.avg_fragmentation_in_percent >= 5
					AND a.page_count > ' + CAST(@min_pages as varchar(100)) + '
					AND LTRIM(RTRIM(ISNULL(i.name, ''''))) <> ''''
		'

--- Retrieve the list of Indexes that didn't fully Defrag
INSERT INTO @tDefrag EXEC sp_executesql @sFragChk2k5
IF EXISTS (SELECT TOP 1 * FROM @tDefrag)
  BEGIN
	--- Reorg/Rebuild based on the status of the index
	DECLARE crsIndex CURSOR FOR
		SELECT		'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(OwnerName) + '.' + QUOTENAME(TableName) + ' ' + defragStyle
						+ (CASE WHEN defragStyle = 'REORGANIZE;'
								THEN 'UPDATE STATISTICS ' + QUOTENAME(OwnerName) + '.' + QUOTENAME(TableName) + ' ' + QUOTENAME(IndexName) + ' WITH FULLSCAN;'
								ELSE '' END)
						+ ' --- ' + CAST(fragPct as varchar(20)) + '% fragmentation' [sql],
					'INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_table_schema, mnt_table_name, mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ @database_name + ''', ''' + OwnerName + ''', ''' + TableName + ''', ''@@DATE@@'', ''index'',' + '@@STATUS@@, ''@@RESULTS@@'')' [auditSQL]
		FROM		@tDefrag
		ORDER BY	defragStyle DESC,
					OwnerName,
					TableName,
					IndexName
	OPEN crsIndex
	FETCH NEXT FROM crsIndex INTO @sSQL, @sAuditSQL
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		BEGIN TRY
			--- Ensure the statement is executed in the desired database
			SELECT @sSQL = 'USE [' + @database_name + ']; ' + @sSQL

			--- Execute the Reorg/Rebuild
			PRINT '   ' + @sSQL
			EXEC sp_executesql @sSQL

			--- Add Success Audit
			SELECT @sAuditSQL = REPLACE(REPLACE(REPLACE(@sAuditSQL, '@@STATUS@@', 1), '@@DATE@@', CONVERT(varchar, GETDATE())), '@@RESULTS@@', 'Command: ' + @sSQL)
			EXEC (@sAuditSQL)
		END TRY
		BEGIN CATCH
			PRINT 'The command failed to execute (' + @sSQL + ')'
			PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
			PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

			--- Add Failure Audit
			SELECT @sAuditSQL = REPLACE(REPLACE(@sAuditSQL, '@@STATUS@@', 0), '@@DATE@@', CONVERT(varchar, GETDATE()))
			IF ERROR_NUMBER() IS NOT NULL
				SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Command: ' + @sSQL + CHAR(13)
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
			ELSE
				SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Command: ' + @sSQL)
			EXEC (@sAuditSQL)
		END CATCH

		FETCH NEXT FROM crsIndex INTO @sSQL, @sAuditSQL
	  END
	CLOSE crsIndex
	DEALLOCATE crsIndex
  END
ELSE
	PRINT 'No indexes found to Reorg/Rebuild in database ' + @database_name

SET NOCOUNT OFF
